package android.mobilin;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;


public class DBAdapter {
    private SQLiteDatabase db;
    /**
     * Database creation sql statement
     */
    
    private static final String FORM_CREATE =
            "create table IF NOT EXISTS form (_id integer primary key autoincrement, "
                    + "refer_no text not null,refer_name text not null, refer_address text not null,"
                    + "refer_telfax text not null, p_name text not null,"
                    + "p_address text not null, p_newaddress text not null,"
                    + "p_caseno text not null, p_startdate text not null,"
                    + "p_category text not null, p_classification text not null,"
                    + "p_type text not null, p_smeardiagnosis text not null,"
                    + "h_b4tx text not null, h_treat text not null, r_treat text not null,"
                    + "z_treat text not null, e_treat text not null, s_treat text not null, "
                    + "smear_b4tx text not null, smear_treat text not null,"
                    + "remarks text not null, h_name text not null,"
                    + "h_designation text not null, referdate text not null);";
    
    private static final String REPLY_CREATE =
	    	"create table IF NOT EXISTS reply (_id integer primary key autoincrement, "
	        + "recv_name text not null, recv_address text not null,"
	        + "recv_telfax text not null, p_newcaseno text not null,"
	        + "h_name text not null, h_designation text not null);";
	
    private static final String SETTING_CREATE =
        	"create table IF NOT EXISTS setting (_id integer primary key autoincrement, "
            + "server_no text not null);";

    private static final String SETTING_DEFAULT =
    	"insert into setting (server_no) VALUES ('+000');";
    
    private static final String DATABASE_NAME = "data";
	
	public DBAdapter(Context context) {
		db=context.openOrCreateDatabase(DATABASE_NAME, Context.MODE_PRIVATE, null);
		/* Create a Table in the Database. */
        db.execSQL(FORM_CREATE);
        db.execSQL(REPLY_CREATE);
        db.execSQL(SETTING_CREATE);
        Cursor sett=db.rawQuery("SELECT * FROM setting",null);
        if(sett!=null) {
        	if(!sett.moveToNext()) {
        		db.execSQL(SETTING_DEFAULT);
        	}
        	sett.close();
        }
	}
	
	
	public boolean insert(String table , String data) {
		String query="";
		String[] elem=data.split("\0");	
		if(table.equals("form")) {
			query="INSERT INTO form"
            + " (refer_number,refer_name,refer_address,refer_telfax,p_name,p_address,"
            + "p_newaddress,p_caseno,p_startdate,p_category,p_classification,"
            + "p_type,p_smeardiagnosis,h_b4tx,h_treat,r_treat,z_treat,e_treat,"
            + "s_treat,smear_b4tx,smear_treat,remarks,h_name,h_designation,referdate)"
            + " VALUES ('"+elem[0]+"','"+elem[1]+"','"+elem[2]+"','"+elem[3]+"','"+elem[4]+"',"
            + "'"+elem[5]+"','"+elem[6]+"','"+elem[7]+"','"+elem[8]+"','"+elem[9]+"','"+elem[10]+"',"
            + "'"+elem[11]+"','"+elem[12]+"','"+elem[13]+"','"+elem[14]+"','"+elem[15]+"',"
            + "'"+elem[16]+"','"+elem[17]+"','"+elem[18]+"','"+elem[19]+"','"+elem[20]+"',"
            + "'"+elem[21]+"','"+elem[22]+"','"+elem[23]+"','"+elem[24]+"');"; 
		}
		else if(table.equals("reply")) {
			query="INSERT INTO form"
	            + " (recv_name,recv_address,recv_telfax,p_newcaseno,h_name,"
	            + "h_designation)"
	            + " VALUES ('"+elem[0]+"','"+elem[1]+"','"+elem[2]+"','"+elem[3]+"','"+elem[4]+"',"
	            + "'"+elem[5]+"');"; 
		}
		else if(table.equals("setting")) {
			query="INSERT INTO setting"
	            + " (server_no)"
	            + " VALUES ('"+elem[0]+"');"; 
			
		}
		Log.d("test",query);
		try {
			db.execSQL(query);
		}
		catch(SQLException e) {
			return false;
		}
		return true;
	}
	
	public Cursor query(String table) {
		String query="SELECT * FROM "+table+" LIMIT 1";
		Cursor res=db.rawQuery(query,null);
		return res;
	}
	
	public boolean remove(String table, String id) {
		String query="DELETE FROM "+table+" WHERE _id="+id;
		try {
			db.execSQL(query);
		}
		catch(SQLException e) {
			return false;
		}
		return true;
	}
	
	public void close() {
		db.close();
	}
}
